﻿
<DataListSpec 
	xmlns="bb_appfx_datalist"
	xmlns:common="bb_appfx_commontypes" 
	ID="f4280c9c-85b9-4d2b-8497-f1e519531db0" 
	Name="ProspectTeamsForConstituent Data List"
	Description="Returns Prospect Team List by Constituent (ProspectID)" 
	Author="Blackbaud Professional Services"
	common:SecurityUIFolder="Service Bus Adapter">



  <!-- define the SP that returns the data for the datalist -->
  <SPDataList SPName="USP_DATALIST_PROSPECTTEAMSFORCONSTITUENT">
    <common:CreateProcedureSQL>
      <![CDATA[
create procedure dbo.USP_DATALIST_PROSPECTTEAMSFORCONSTITUENT(
  @CONSTITUENTID uniqueidentifier)
as
	set nocount on;

  SELECT
  cp.ID,
  cp.LOOKUPID as PROSPECTLOOKUPID,
  cm.LOOKUPID as MEMBERLOOKUPID,
  pt.DATEFROM, pt.DATETO,
  ptr.DESCRIPTION AS PROSPECTTEAMROLE,
  pt.DATEADDED,
  pt.DATECHANGED,
  ca.USERNAME as CHANGEDBYUSERNAME 

  FROM PROSPECTTEAM pt
  INNER JOIN CONSTITUENT cp ON pt.PROSPECTID = cp.ID
  INNER JOIN CONSTITUENT cm ON pt.MEMBERID = cm.ID
  INNER JOIN PROSPECTTEAMROLECODE ptr ON pt.PROSPECTTEAMROLECODEID = ptr.ID
  LEFT OUTER JOIN CHANGEAGENT     ca  ON ca.ID                = pt.CHANGEDBYID  
  WHERE pt.PROSPECTID = @CONSTITUENTID

UNION

  SELECT
  cp.ID,
  cp.LOOKUPID as PROSPECTLOOKUPID,
  cm.LOOKUPID as MEMBERLOOKUPID,
  p.PROSPECTMANAGERSTARTDATE, p.PROSPECTMANAGERENDDATE,
  'Primary' AS PROSPECTTEAMROLE,
  p.DATEADDED,
  p.DATECHANGED,
  ca.USERNAME as CHANGEDBYUSERNAME 

  FROM PROSPECT p
  INNER JOIN CONSTITUENT cp ON p.ID = cp.ID
  INNER JOIN CONSTITUENT cm ON p.PROSPECTMANAGERFUNDRAISERID = cm.ID
  --INNER JOIN PROSPECTTEAMROLECODE ptr ON pt.PROSPECTTEAMROLECODEID = ptr.ID
  LEFT OUTER JOIN CHANGEAGENT     ca  ON ca.ID                = p.CHANGEDBYID  
  WHERE p.ID = @CONSTITUENTID




			]]>
    </common:CreateProcedureSQL>
  </SPDataList>

  <!-- describe any parameters (other than the context ID) defined on the SP -->
  <Parameters>
    <common:FormMetaData>
      <common:FormFields>
        <common:FormField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid"/>
      </common:FormFields>
    </common:FormMetaData>
  </Parameters>

  <!-- define the output fields in the list -->
  <Output>
    <OutputFields>
      <!-- 0 -->
      <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
      <!-- 1 -->
      <OutputField FieldID="PROSPECTLOOKUPID" Caption="Prospect Lookup ID" DataType="String" />
      <!-- 2 -->
      <OutputField FieldID="MEMBERLOOKUPID" Caption="Member Lookup ID" DataType="String" />
      <!-- 3 -->
      <OutputField FieldID="DATEFROM" Caption="Date From" DataType="String" />
      <!-- 4 -->
      <OutputField FieldID="DATETO" Caption="Date To" DataType="String" />
      <!-- 5 -->
      <OutputField FieldID="PROSPECTTEAMROLE" Caption="Prospect Team Role" DataType="String" />
      <!-- 6 -->
      <OutputField FieldID="DATEADDED" Caption="Date Added" DataType="Date" />
      <!-- 7 -->
      <OutputField FieldID="DATECHANGED" Caption="Date Changed" DataType="Date" />
      <!-- 8 -->
      <OutputField FieldID="CHANGEDBYUSERNAME" Caption="Changed by User Name" DataType="String" />
    </OutputFields>
  </Output>
</DataListSpec>
